Release 10.1A: OpenEdge Data Management:
SQL Development

Working with locking behavior and isolation levels

Consistency and concurrency are maintained with record locks. Because SQL relies on table and record locks to carry out the intent of transaction isolation levels, table locks were implemented in the database engine. Both 4GL and SQL clients encounter table locks while executing transactions.

A locking conflict occurs when two transactions request the same resource at the same time. The SQL client will wait for a resource for a specified time before giving up, at which point an error would be generated and the operation would need to be retried. The default wait time is five seconds, but can be modified to a duration that meets you application’s needs.

For 4GL clients, there is a lock wait timeout parameter (-lkwtmo) that specifies how long a client should wait for a resource. The current default value is 30 minutes. If a SQL client has a lock on a table for which the 4GL client also requested a lock, the SQL client will time-out and give up waiting long before the 4GL client. SQL can be set by using the environment variable PRO_LOCKWAIT_TIMEOUT.

A SELECT statement can fail if some records of the selected tables are locked by other transactions. The SELECT transaction is not able to continue until the record locks are released by other transactions. The READPAST lock hint causes a transaction to skip rows locked by other transactions. The skipped rows do not appear in the result set, and a warning is returned to the client.

The following conditions apply to the READPAST locking hint:

Applies only to the SELECT statement.
Applies only to transactions operating at READ COMMITTED isolation.
Will read only past row-level locks.
Can only be specified in the main SELECT statement but not in the subquery SELECT statement in the search_condition of the WHERE clause.

For detailed information on record locks and the READPAST locking hint, see Chapter 8, " Data Control Language and Transaction Behavior."